﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DIProject.Data
{
   public class Assortment
    {
       private static DataSets.Dictionary.AssortmentSmallDataTable _data = null;
       public static DataSets.Dictionary.AssortmentSmallDataTable Data
       {
           get
           {
               if (_data == null)
               {
                   DataSets.DictionaryTableAdapters.AssortmentSmallTableAdapter adapter = new DIProject.DataSets.DictionaryTableAdapters.AssortmentSmallTableAdapter();
                   _data = adapter.GetData();
               }
               return _data;
           }
       }
       public static void ClearData()
       {
           _data = null;
       }
       /// <summary>
       /// Добавляем ассортимент
       /// </summary>
       /// <param name="catalogId"></param>
       /// <param name="name"></param>
       /// <param name="medFormId"></param>
       /// <param name="assesmentGroupId"></param>
       /// <param name="aBList"></param>
       /// <param name="notPatentedRus"></param>
       /// <param name="notPatentedLat"></param>
       /// <param name="gNVLS"></param>
       /// <param name="mandatory"></param>
       /// <param name="storeTemperature"></param>
       /// <param name="beforeOverdue"></param>
       /// <param name="noRecipe"></param>
       /// <param name="referentPrice"></param>
       /// <param name="referentDate"></param>
       /// <param name="note"></param>
       /// <param name="mD"></param>
       /// <param name="mW"></param>
       /// <returns></returns>
       public static Int32 AddAssortment(Int32 catalogId, string name, Int32 medFormId, Int32? assesmentGroupId,
           string aBList, string notPatentedRus, string notPatentedLat, bool? gNVLS, bool mandatory, string storeTemperature,
           byte? beforeOverdue, bool? noRecipe, decimal? referentPrice, DateTime? referentDate, string note,
           DateTime mD, string mW)
       {
           string QueryString = @"dbo.AssortmentInsert";
           string connStr = Properties.Settings.Default.DIAptekaConnectionString;
           using (SqlConnection connection = new SqlConnection(connStr))
           {
               try
               {
                   SqlCommand command = new SqlCommand(QueryString, connection);
                   command.CommandType = CommandType.StoredProcedure;
                   command.Parameters.AddWithValue("@CatalogId", catalogId);
                   command.Parameters.AddWithValue("@Name", name);
                   command.Parameters.AddWithValue("@MedFormId", medFormId);
                   if (assesmentGroupId.HasValue)
                       command.Parameters.AddWithValue("@AssesmentGroupId", assesmentGroupId);
                   else
                       command.Parameters.AddWithValue("@AssesmentGroupId", DBNull.Value);
                   if (!string.IsNullOrEmpty(aBList))
                       command.Parameters.AddWithValue("@ABList", aBList);
                   else
                       command.Parameters.AddWithValue("@ABList", DBNull.Value);
                   if (!string.IsNullOrEmpty(notPatentedRus))
                       command.Parameters.AddWithValue("@NotPatentedRus", notPatentedRus);
                   else
                       command.Parameters.AddWithValue("@NotPatentedRus", DBNull.Value);
                   if (!string.IsNullOrEmpty(notPatentedLat))
                       command.Parameters.AddWithValue("@NotPatentedLat", notPatentedLat);
                   else
                       command.Parameters.AddWithValue("@NotPatentedLat", DBNull.Value);
                   if (gNVLS.HasValue)
                       command.Parameters.AddWithValue("@GNVLS", gNVLS);
                   else
                       command.Parameters.AddWithValue("@GNVLS", DBNull.Value);
                   command.Parameters.AddWithValue("@Mandatory", mandatory);
                   if (!string.IsNullOrEmpty(storeTemperature))
                       command.Parameters.AddWithValue("@StoreTemperature", storeTemperature);
                   else
                       command.Parameters.AddWithValue("@StoreTemperature", DBNull.Value);
                   if(beforeOverdue.HasValue)
                       command.Parameters.AddWithValue("@BeforeOverdue",beforeOverdue);
                   else
                       command.Parameters.AddWithValue("@BeforeOverdue", DBNull.Value);
                   if (noRecipe.HasValue)
                       command.Parameters.AddWithValue("@NoRecipe", noRecipe);
                   else
                       command.Parameters.AddWithValue("@NoRecipe", DBNull.Value);
                   if (referentPrice.HasValue)
                       command.Parameters.AddWithValue("@ReferentPrice", referentPrice);
                   else
                       command.Parameters.AddWithValue("@ReferentPrice", DBNull.Value);
                   if (referentDate.HasValue)
                       command.Parameters.AddWithValue("@ReferentDate", referentDate);
                   else
                       command.Parameters.AddWithValue("@ReferentDate", DBNull.Value);
                   if (string.IsNullOrEmpty(note))
                       command.Parameters.AddWithValue("@Note", note);
                   else
                       command.Parameters.AddWithValue("@Note", DBNull.Value);
                   command.Parameters.AddWithValue("@MD", mD);
                   command.Parameters.AddWithValue("@MW", mW);
                   connection.Open();
                   SqlDataReader dr = command.ExecuteReader();
                   Int32 id = dr.GetOrdinal("Id");
                   if (!dr.Read())
                   {
                       throw new Util.DIException("Невозможно добавить ассортимент");
                   }
                   Int32 result = dr.GetInt32(id);
                   return result;

               }
               catch (SqlException se)
               {
                   throw new Util.DIException("Не удалось добавить ассортимент", se);
               }
               catch (Exception ee)
               {
                   throw new Util.DIException("Не удалось добавить ассортимент", ee);
               }
           }
       }
       /// <summary>
       /// Обновляем ассотимент
       /// </summary>
       /// <param name="catalogId"></param>
       /// <param name="name"></param>
       /// <param name="medFormId"></param>
       /// <param name="assesmentGroupId"></param>
       /// <param name="aBList"></param>
       /// <param name="notPatentedRus"></param>
       /// <param name="notPatentedLat"></param>
       /// <param name="gNVLS"></param>
       /// <param name="mandatory"></param>
       /// <param name="storeTemperature"></param>
       /// <param name="beforeOverdue"></param>
       /// <param name="noRecipe"></param>
       /// <param name="referentPrice"></param>
       /// <param name="referentDate"></param>
       /// <param name="note"></param>
       /// <param name="mD"></param>
       /// <param name="mW"></param>
       /// <param name="toDelete"></param>
       /// <param name="original_Id"></param>
       /// <param name="original_MD"></param>
       /// <returns></returns>
       public static Int32 ModifiAssortment(Int32 catalogId, string name, Int32 medFormId, Int32? assesmentGroupId,
           string aBList, string notPatentedRus, string notPatentedLat, bool? gNVLS, bool mandatory, string storeTemperature,
           byte? beforeOverdue, bool? noRecipe, decimal? referentPrice, DateTime? referentDate, string note,
           DateTime mD, string mW, bool toDelete, Int32 original_Id, DateTime original_MD)
       {
           string QueryString = @"dbo.AssortmentUpdate";
           string connStr = Properties.Settings.Default.DIAptekaConnectionString;
           using (SqlConnection connection = new SqlConnection(connStr))
           {
               try
               {
                   SqlCommand command = new SqlCommand(QueryString, connection);
                   command.CommandType = CommandType.StoredProcedure;
                   command.Parameters.AddWithValue("@CatalogId", catalogId);
                   command.Parameters.AddWithValue("@Name", name);
                   command.Parameters.AddWithValue("@MedFormId", medFormId);
                   if (assesmentGroupId.HasValue)
                       command.Parameters.AddWithValue("@AssesmentGroupId", assesmentGroupId);
                   else
                       command.Parameters.AddWithValue("@AssesmentGroupId", DBNull.Value);
                   if (!string.IsNullOrEmpty(aBList))
                       command.Parameters.AddWithValue("@ABList", aBList);
                   else
                       command.Parameters.AddWithValue("@ABList", DBNull.Value);
                   if (!string.IsNullOrEmpty(notPatentedRus))
                       command.Parameters.AddWithValue("@NotPatentedRus", notPatentedRus);
                   else
                       command.Parameters.AddWithValue("@NotPatentedRus", DBNull.Value);
                   if (!string.IsNullOrEmpty(notPatentedLat))
                       command.Parameters.AddWithValue("@NotPatentedLat", notPatentedLat);
                   else
                       command.Parameters.AddWithValue("@NotPatentedLat", DBNull.Value);
                   if (gNVLS.HasValue)
                       command.Parameters.AddWithValue("@GNVLS", gNVLS);
                   else
                       command.Parameters.AddWithValue("@GNVLS", DBNull.Value);
                   command.Parameters.AddWithValue("@Mandatory", mandatory);
                   if (!string.IsNullOrEmpty(storeTemperature))
                       command.Parameters.AddWithValue("@StoreTemperature", storeTemperature);
                   else
                       command.Parameters.AddWithValue("@StoreTemperature", DBNull.Value);
                   if (beforeOverdue.HasValue)
                       command.Parameters.AddWithValue("@BeforeOverdue", beforeOverdue);
                   else
                       command.Parameters.AddWithValue("@BeforeOverdue", DBNull.Value);
                   if (noRecipe.HasValue)
                       command.Parameters.AddWithValue("@NoRecipe", noRecipe);
                   else
                       command.Parameters.AddWithValue("@NoRecipe", DBNull.Value);
                   if (referentPrice.HasValue)
                       command.Parameters.AddWithValue("@ReferentPrice", referentPrice);
                   else
                       command.Parameters.AddWithValue("@ReferentPrice", DBNull.Value);
                   if (referentDate.HasValue)
                       command.Parameters.AddWithValue("@ReferentDate", referentDate);
                   else
                       command.Parameters.AddWithValue("@ReferentDate", DBNull.Value);
                   if (string.IsNullOrEmpty(note))
                       command.Parameters.AddWithValue("@Note", note);
                   else
                       command.Parameters.AddWithValue("@Note", DBNull.Value);
                   command.Parameters.AddWithValue("@MD", mD);
                   command.Parameters.AddWithValue("@MW", mW);
                   command.Parameters.AddWithValue("@ToDelete", toDelete);
                   command.Parameters.AddWithValue("@Original_Id", original_Id);
                   command.Parameters.AddWithValue("@Original_MD", original_MD);
                   connection.Open();
                   SqlDataReader dr = command.ExecuteReader();
                   Int32 id = dr.GetOrdinal("Id");
                   if (!dr.Read())
                   {
                       throw new Util.DIException("Невозможно добавить ассортимент");
                   }
                   Int32 result = dr.GetInt32(id);
                   return result;

               }
               catch (SqlException se)
               {
                   throw new Util.DIException("Не удалось добавить ассортимент", se);
               }
               catch (Exception ee)
               {
                   throw new Util.DIException("Не удалось добавить ассортимент", ee);
               }
           }
       }
       /// <summary>
       /// Удаляем
       /// </summary>
       /// <param name="original_Id"></param>
       /// <param name="original_MD"></param>
       /// <param name="mW"></param>
       /// <returns></returns>
       public static Int32 DeleteAssortment(Int32 original_Id, DateTime original_MD, string mW)
       {
           string QueryString = @"dbo.AssortmentDelete";
           string connStr = Properties.Settings.Default.DIAptekaConnectionString;
           using (SqlConnection connection = new SqlConnection(connStr))
           {
               try
               {
                   SqlCommand command = new SqlCommand(QueryString, connection);
                   command.CommandType = CommandType.StoredProcedure;
                   command.Parameters.AddWithValue("@MW", mW);
                   command.Parameters.AddWithValue("@Original_Id", original_Id);
                   command.Parameters.AddWithValue("@Original_MD", original_MD);
                   connection.Open();
                   Int32 res = command.ExecuteNonQuery();
                   return res;
               }
               catch (SqlException se)
               {
                   throw new Util.DIException("Не удалось удалить ассортимент", se);
               }
               catch (Exception ee)
               {
                   throw new Util.DIException("Не удалось удалить ассортимент", ee);
               }
           }
       }
    }
}
